﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using log4net;

using System.Xml;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk;
using Microsoft.SqlServer.Management.Common;

namespace BILAB.Business.History.Core
{       

    public class TablePath
    {
        #region Log4net
        private static readonly ILog log = LogManager.GetLogger("TablePath");
        #endregion

        #region private members &properties
        Server _server;    
        Database _database;
        bool _isConnected;
        string _tableName;
        string _primaryKey;
        string _schema;
        string _parentKey;
        List<TablePath> _childs = new List<TablePath>();
        int _depth = -1;
        #endregion


        #region Properties
        public int Depth
        {
            get {
                if (_depth == -1)
                {
                }             
                return _depth; 
            }            
        }

        public List<TablePath> Childs
        {
            get
            {
                return _childs;
            }

        }


        public string ParentKey
        {
            get { return _parentKey; }
            set { _parentKey = value; }
        }

        private static List<string> _alreadyCaught = new List<string>();

        public string Schema
        {
            get { return _schema; }
            set { _schema = value; }
        }

        public string PrimaryKey
        {
            get { return _primaryKey; }
            set { _primaryKey = value; }
        }

        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }
        #endregion
        
        #region DB Connection
        public List<string> GetDatabaseList(string server, ServerAuthMode mode, string user, string password)
        {
            List<string> dblist = new List<string>();           
            ServerConnection sc = null;
            Server sqlserver;

            log.Info("connecting");


            // Sets appropriate connections proerties accordign db context
            if (mode == ServerAuthMode.SqlServer)
            {
                sc = new ServerConnection(server, user, password);
            }
            else if (mode == ServerAuthMode.Integrated)
            {
                sc = new ServerConnection(server);
            }


            try
            {
                sqlserver = new Server(sc);
                log.Info("Creating db list");
                foreach (Database db in sqlserver.Databases)
                {
                    log.Info("adding " + db.Name + " to the list ");
                    dblist.Add(db.Name);
                }
            }
            catch (Exception e)
            {
                log.Debug("Error obtaining database list connecting : " + e.Message);
            }
            finally
            {
                sc = null;
                _server = null;
            }            
            
            return dblist;
        }
        public bool ConnectToDatabase(string server, string database, ServerAuthMode mode, string user, string password)
        {
            bool state = false;
            ServerConnection sc = new ServerConnection();

            log.Info("connecting");

            try
            {
                log.Info("connect to " + server);
                if (mode == ServerAuthMode.SqlServer)
                {
                     sc = new ServerConnection(server, user, password);
                }
                else if (mode == ServerAuthMode.Integrated)
                {
                    sc = new ServerConnection(server);                    
                }

                _server = new Server(sc);              


                log.Info("obtain database " + database);
                _database = _server.Databases[database];

                log.Info("Connected to " + server + @"\" + database);
                state = true;
            }
            catch (Exception e)
            {
                log.Debug(e.Message);
                state = false;
            }

            this._isConnected = state;
            return state;
        }
        #endregion

        #region Database Analysis
        public TablePath AnalyseDatabaseBy(string tableName,string schema)
        {   
            /* Sets main information */
            this.TableName = tableName;
            this.Schema = schema;

            /* Build a representation of any PrimaryKey */

            /* Look for Childs */
            this._childs = this.lookForChilds( this,this);

            return this;
        }

        private List<TablePath> lookForChilds( TablePath root ,   TablePath parent)
        {
            List<TablePath> ltp =  new List<TablePath>();
           
            DataTable dt = _database.Tables[parent.TableName,parent.Schema].EnumForeignKeys();
            foreach (DataRow dr in dt.Rows)
            {
                string tableName = dr[1].ToString();
                string schemaName = dr[0].ToString(); ;

                if (!(tableName.Equals(parent.TableName) &&
                      schemaName.Equals(parent.Schema))
                    )
                {
                    log.Info("scanning now " + tableName);
                    TablePath tp = new TablePath();
                    tp._database = parent._database;
                    tp._server = parent._server;
                    tp.TableName = dr[1].ToString();
                    tp.Schema = schemaName;
                    tp.ParentKey = parent.PrimaryKey;                    
                    // compute primary key of the table


                    Table concernedTable = _database.Tables[tableName];

                    ColumnCollection cols = _database.Tables[tableName, schemaName].Columns;
                    StringBuilder sb = new StringBuilder();
                    foreach (Column col in cols)
                    {
                        if (col.InPrimaryKey)
                        {
                            sb.Append("[");
                            sb.Append(col.Name);
                            sb.Append("]");
                        }
                    }
                    tp.PrimaryKey = sb.ToString();


                    tp._childs = this.lookForChilds(root, tp);                    
                    ltp.Add(tp);
                }
                else
                {
                    log.Info("preventing infiniteRecursion on " + tableName);
                }
                   
                
            }          
            return ltp;
        }
        #endregion

        #region tablePath operation
        private int analyseDepth()
        {
            int depth = -1;
            if ( this.Childs.Count!= 0)
            {


            }
            return depth;
        }
        #endregion




    }
}
